let db =require('../db');

//家庭信息列表
exports.familyList = async function (userId) {
 let sql=`
 SELECT *,
 DATE_FORMAT(createdAt,'%Y-%m-%d %H:%i') AS createdAt

FROM family  WHERE isdelete ='n' AND  userId= :userId ORDER BY  createdAt DESC


 `   
 let data  = await db.query(sql,{userId});
 return data[0];
}

//获取住户信息

exports.getResidents  = async function(userId){
   let sql=`
   SELECT 
   us.id,
   us.unitId,
   us.realName,
   us.phone,
   us.houseNumber,  
    vi.name AS villageName,
    bu.name AS buildingName,
    un.name  AS unitName 
   FROM 
   (SELECT *FROM user WHERE isdelete='n' AND id=:userId) AS us
   LEFT JOIN village  AS vi ON vi.id  = us.villageId
   LEFT JOIN building  AS bu ON bu.id = us.buildingId
   LEFT JOIN unit AS un  ON un.id = us.unitId

   ` 
   let data  = await db.query(sql,{userId});
   return data[0][0];
}

//添加成员信息

exports.addFamily = async function(name,img,userId,unitId,synchronous){
    let sql=`
    INSERT  INTO  family(name,userId,url,unitId,synchronous)
    VALUES(:name,:userId,:img,:unitId,:synchronous)
    `

    let data  = await db.query(sql,{name,img,userId,unitId,synchronous});
    return data[0];
}

//修改成员信息

exports.updateFamily  = async function(id,name,url){
    let sql=`
    UPDATE family SET
    name=:name,
    url=:url
    where id =:id
    `
    let data  = await db.query(sql,{id,name,url});
    return data[0];
}

//删除成员
exports.removeFamily = async function(familyId){
    let sql=`
    UPDATE  family SET  
    isdelete ='y'
    where id =:familyId
    `
    let data  = await db.query(sql,{familyId});
    return data[0];
}

//获取新添加住户家庭信息
exports.getNewFamily = async function(id){
  let sql=`
  SELECT
   id, 
   name,
   url,
   unitId,
   isdelete
  FROM family  WHERE  id= :id  `

  let data = await db.query(sql,{id});
  return data[0];
}

//获取该住户家庭成员新增信息

exports.getNewImages  = async function(userId){
    let sql=`
    SELECT
    id, 
    name,
    url,
    unitId,
    isdelete
   FROM family  WHERE  userId= :userId  AND synchronous ='n' AND  isdelete ='n'
    `
   let data  = await db.query(sql,{userId});
   return data[0]; 
}